/*

exec sp_planning_report_detail_by_backLog 0, 0,'ALL'

*/
ALTER PROC sp_planning_report_detail_by_backLog

@BASEPRODUCT_ID	INT,
@PS_STEP_ID	INT,
@CHIP_SET_VALUE   VARCHAR(20)
as
begin
	declare @RESULTS_TABLE table
		( [Customer Name] varchar(100), [Ship Req No] varchar(50),[Order No] VARCHAR(50), [Line No] VARCHAR(50), [Ship From Loc] VARCHAR(50), 
		   [Mkt Part Number] varchar(50), [Mfg Part Number] varchar(50),[Part Status] VARCHAR(50),[Request Date] DATETIME,[Schedule Ship Date] DATETIME, [Status] VARCHAR(10), [Ordered Qty] INT,
		  [FG] int,[EOL] int,[FT] int,[Assembly] int,[Assm Release Qty] INT,[Die Bank] int,[Sort] int,[Fab] int,[ChipSet] VARCHAr(20), [Days to Ship] VARCHAR(50), [Planner] VARCHAR(25)
		 		 
		)
	
	declare @baseproduct	varchar(50)
	declare @mktpartnumber	varchar(50)
	declare @fabpartnumber 	varchar(50)
	declare @fgprocessstep_id int
	declare @product_id	int
	declare @processstep_id	int
	declare @ps_type	varchar(50)
	declare @fab_qty	int
	declare @wafer_bank_qty	int
	declare @sort_qty	int
	declare @diebank_qty	int
	declare @assembly_qty	int,@assembly_release_qty int
	declare @pre_test_qty	int
	declare @ft_qty		int
	declare @eol_qty	int
	declare	@fg_qty		int
	declare @ship_qty	int
	DECLARE @CHIPSET    VARCHAR(20)
	DECLARE @PART_NUMBER VARCHAR(50)
	declare @lot_cursor	cursor
	declare @assm_yield float
	declare @test_yield float
	DECLARE @CURRENT_QUARTER INT
	DECLARE @planner varchar(25)
	DECLARE @Part_Status VARCHAR(50)

	SELECT @CURRENT_QUARTER = CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR WHERE CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101 ) >= CQC_START_DATE AND CONVERT( VARCHAR, CURRENT_TIMESTAMP, 101) <= CQC_END_DATE
	
	DECLARE @CURSOR_STRING NVARCHAR(1000)

	 SET @CURSOR_STRING = N'DECLARE product_cursor CURSOR FOR SELECT DISTINCT p_marketing_part_number, p.Processstep_id,
				(select part_number from product where product_id = p.p_parent_product_id ), P.product_id, Part_Number,Part_Status
				 FROM Customer_Open_Order_LineItem COOL, SALES_ORDER SO, CUSTOMER C, PRODUCT P WHERE
				P.PRODUCT_ID = COOL.PRODUCT_ID AND COOL.SalesOrder_ID = SO.SalesOrder_ID AND SO.Customer_ID = C.Customer_ID AND COOL_STATUS <> ''SHIPPED'' '
	
	IF @BASEPRODUCT_ID <> '0' 
	BEGIN
		SET @CURSOR_STRING = @CURSOR_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASEPRODUCT_ID)
	END
	
	IF @PS_STEP_ID <> '0' 
	BEGIN
		SET @CURSOR_STRING = @CURSOR_STRING + ' AND P.ProcessStep_ID = '+ CONVERT(VARCHAR, @PS_STEP_ID)
	END
	
	--PRINT @CURSOR_STRING

	EXECUTE SP_EXECUTESQL @CURSOR_STRING
	
	OPEN product_cursor
	FETCH NEXT FROM product_cursor INTO @mktpartnumber, @fgprocessstep_id, @baseproduct, @product_id, @PART_NUMBER,@Part_Status
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @CHIPSET = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'ChipSET1' AND PROCESSSTEP_ID = @fgprocessstep_id				
		set @lot_cursor = cursor for
		select distinct processstep_id,ps_type from FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@fgprocessstep_id,0)
		OPEN @lot_cursor
		FETCH NEXT FROM @lot_cursor INTO @processstep_id,@ps_type
		WHILE @@FETCH_STATUS = 0
		BEGIN
			if( @ps_type = 'fab')
			begin
				set @ship_qty = null
				select @fab_qty = sum(wafer_qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'
				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'
				set @fab_qty = isnull(@fab_qty,0) + isnull(@ship_qty,0)
			end
			else if( @ps_type = 'Wafer Bank')
			begin
				set @ship_qty = null
				select @wafer_bank_qty = sum(wafer_qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'

				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'

				set @wafer_bank_qty = isnull(@wafer_bank_qty,0) + isnull(@ship_qty,0)
			end
			else if( @ps_type = 'Sort')
			begin
				set @ship_qty = null
				select @sort_qty = sum(wafer_qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'

				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'

				set @sort_qty = isnull(@sort_qty,0) + isnull(@ship_qty,0)
			end
			else if( @ps_type = 'die bank')
			begin
				set @ship_qty = null
				select @diebank_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'

				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'

				set @diebank_qty = isnull(@diebank_qty,0) + isnull(@ship_qty,0)
			end
			else if( @ps_type = 'assembly')
			begin
				set @ship_qty = null
				select @assembly_release_qty = Sum(Qty_PULLED) from assm_release_lot_table arlt , Assm_release_table art where Art.assembly_release_id= ARLT.assembly_release_id
					AND Processstep_id =@processstep_id and status = 'Approved' 

				select @assembly_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'
				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'
				set @assembly_qty = isnull(@assembly_qty,0) + isnull(@ship_qty,0)
				SELECT @assm_yield = (CASE WHEN @CURRENT_QUARTER = 1 THEN Q1_yield WHEN @CURRENT_QUARTER = 2 then Q2_Yield WHEN @CURRENT_QUARTER = 3 then Q3_Yield ELSE Q4_Yield END) FROM Update_View_Cost_Table where ProcessStep_Id = @processstep_id AND Year(Fin_Year) = Year(CURRENT_TIMESTAMP)
				SELECT @test_yield = (CASE WHEN @CURRENT_QUARTER = 1 THEN Q1_yield WHEN @CURRENT_QUARTER = 2 then Q2_Yield WHEN @CURRENT_QUARTER = 3 then Q3_Yield ELSE Q4_Yield END) FROM Update_View_Cost_Table where ProcessStep_Id = ( Select Processstep_Id from FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @processstep_id, 1) where PS_Type = 'FT' ) AND Year(Fin_Year) = Year(CURRENT_TIMESTAMP)
				IF @assm_yield IS NULL OR @assm_yield <= 0
				BEGIN
					SET @assm_yield = 100
				END

				IF @test_yield IS NULL OR @test_yield <= 0
				BEGIN
					SET @test_yield = 100
				END
				SET @assembly_qty = (@assembly_qty*(@assm_yield/100))*(@test_yield/100)

			end
			else if( @ps_type = 'Pre Test')
			begin
				set @ship_qty = null
				select @pre_test_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'
				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'

				set @pre_test_qty = isnull(@pre_test_qty,0) + isnull(@ship_qty,0)
				SELECT @test_yield = (CASE WHEN @CURRENT_QUARTER = 1 THEN Q1_yield WHEN @CURRENT_QUARTER = 2 then Q2_Yield WHEN @CURRENT_QUARTER = 3 then Q3_Yield ELSE Q4_Yield END) FROM Update_View_Cost_Table where ProcessStep_Id = ( Select Processstep_Id from FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @processstep_id, 1) where PS_Type = 'FT' ) AND Year(Fin_Year) = Year(CURRENT_TIMESTAMP)

				IF @test_yield IS NULL OR @test_yield <= 0
				BEGIN
					SET @test_yield = 100
				END
				SET @pre_test_qty = (@pre_test_qty*(@test_yield/100))

			end
			else if( @ps_type = 'ft')
			begin
				set @ship_qty = null
				select @ft_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'

				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'

				set @ft_qty = isnull(@ft_qty,0) + isnull(@ship_qty,0)
				SELECT @test_yield = (CASE WHEN @CURRENT_QUARTER = 1 THEN Q1_yield WHEN @CURRENT_QUARTER = 2 then Q2_Yield WHEN @CURRENT_QUARTER = 3 then Q3_Yield ELSE Q4_Yield END) FROM Update_View_Cost_Table where ProcessStep_Id = @processstep_id AND Year(Fin_Year) = Year(CURRENT_TIMESTAMP)

				IF @test_yield IS NULL OR @test_yield <= 0
				BEGIN
					SET @test_yield = 100
				END
				SET @ft_qty = (@ft_qty*(@test_yield/100))
			end
			else if( @ps_type = 'EOL')
			begin
				set @ship_qty = null
				select @eol_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'

				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'

				set @eol_qty = isnull(@eol_qty,0) + isnull(@ship_qty,0)
			end
			else if( @ps_type = 'fg' and @fgprocessstep_id = @processstep_id)
			begin
				set @ship_qty = null
				SET @planner = ''
				SELECT @planner = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Planner' AND PROCESSSTEP_ID = @fgprocessstep_id
				select @fg_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'
				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close' and ISNULL( Lot_category, 'PROD' ) <> 'ENGG'
				set @fg_qty = isnull(@fg_qty,0) + isnull(@ship_qty,0)
			end
		FETCH NEXT FROM @lot_cursor INTO @processstep_id,@ps_type
		END
		CLOSE @lot_cursor
		DEALLOCATE @lot_cursor
				
		INSERT INTO @RESULTS_TABLE ([Mkt Part Number],[Mfg Part Number],[Part Status],[FG],[EOL],[FT],[Assembly],[Assm Release Qty],[Die Bank],[Sort],[Fab],[ChipSet], [Planner])
		VALUES
		(@mktpartnumber,@PART_NUMBER,@Part_Status,@fg_qty,@eol_qty,(@ft_qty +@pre_test_qty),@assembly_qty,@assembly_release_qty,@diebank_qty, (ISNULL(@sort_qty,0)+ isNULL(@wafer_bank_qty,0)) ,@fab_qty ,@CHIPSET, @planner)

		INSERT INTO @RESULTS_TABLE( [Customer Name],[Ship Req No],[Order No],[Line No],[Ship From Loc],[Request Date],
		[Mkt Part Number],[Mfg Part Number],[Ordered Qty],[Schedule Ship Date],[ChipSet], [Status] )
		SELECT C_NAME,ShipReqNum,SalesOrder_No,CONVERT(VARCHAR,COOL_Line_Item_No)+'.'+CONVERT(VARCHAR, COOL_Shipment_No),(SELECT L_Vendor_Name FROM Location Where Location_ID = SOSLT.Shipping_Location_ID),COOL_requested_Date,@mktpartnumber,@PART_NUMBER,
		COOL_Qty-(isnull( (SELECT BackLog_Qty FROM  Customer_Pending_Order_LineItem CPOL WHERE CPOL.LineItem_ID = COOL.LineItem_ID),0) )
		,ISNULL(COOL_revised_Date, '01/01/9999' ),@CHIPSET, CASE WHEN COOL_requested_Date < ISNULL(COOL_revised_Date, COOL_requested_Date ) THEN 'LATE' ELSE 'ON TIME' END
				FROM Customer_Open_Order_LineItem COOL LEFT Outer Join SALES_ORDER_SHIPPING_LOCATIONS_TABLE SOSLT ON COOL.LineItem_ID = SOSLT.LineItem_ID, SALES_ORDER SO, CUSTOMER C, PRODUCT P WHERE
		P.PRODUCT_ID = COOL.PRODUCT_ID AND COOL.SalesOrder_ID = SO.SalesOrder_ID AND SO.Customer_ID = C.Customer_ID 
		AND P.PRODUCT_ID = @product_id AND COOL_STATUS <> 'Shipped'
		
		
		SET @CHIPSET		= null
		SET @PART_NUMBER    = null
		set @baseproduct	= null
		set @mktpartnumber	= null
		set @fgprocessstep_id 	= null
		set @processstep_id	= null
		SET @product_id		= null
		set @ps_type		= null
		set @fab_qty		= null
		set @wafer_bank_qty	= null
		set @sort_qty	= null
		set @diebank_qty	= null
		set @assembly_qty	= null
		set @pre_test_qty	= null
		set @ft_qty		= null
		set @eol_qty		= null
		set @fg_qty		= null
		Set @Part_Status = null		
		SET @assembly_release_qty = null	
	FETCH NEXT FROM product_cursor INTO @mktpartnumber,@fgprocessstep_id,@baseproduct,@product_id,@PART_NUMBER,@Part_Status
	END
	CLOSE product_cursor
	DEALLOCATE product_cursor

	DECLARE @Result_Cursor CURSOR
	DECLARE @Ship_Req_No VARCHAR(50)
	DECLARE @Order_No VARCHAR(50)
	DECLARE @Line_No VARCHAR(50)
	DECLARE @Order_Qty INT
	DECLARE @FG_Qty_Map INT
	DECLARE @EOL_Qty_Map INT
	DECLARE @FT_Qty_Map INT
	DECLARE @Assm_Qty_Map INT
	DECLARE @DaysToShip VARCHAR(50)
	DECLARE @shecdule_Date DATETIME
 	
	SET @Result_Cursor = CURSOR FOR SELECT [Ship Req No], [Order No], [Line No], [Mfg Part Number], [Ordered Qty],
		  [FG],[EOL],[FT],[Assembly], [Schedule Ship Date] FROM @RESULTS_TABLE ORDER BY [Mfg Part Number],[Schedule Ship Date],[Order No],[Line No]

	OPEN @Result_Cursor
	FETCH NEXT FROM @Result_Cursor INTO @Ship_Req_No, @Order_No, @Line_No, @PART_NUMBER, @Order_Qty, @fg_qty, @eol_qty, @ft_qty, @assembly_qty, @shecdule_Date
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @Order_No IS NULL AND @Line_No IS NULL
		BEGIN
			SET @FG_Qty_Map = ISNULL(@fg_qty, 0)
			SET @EOL_Qty_Map = @FG_Qty_Map + ISNULL(@eol_qty,0)
			SET @FT_Qty_Map = @EOL_Qty_Map + ISNULL( @ft_qty,0)
			SET @Assm_Qty_Map = @FT_Qty_Map + ISNULL( @assembly_qty,0)
		END
		ELSE
		BEGIN

			SET @FG_Qty_Map = @FG_Qty_Map - @Order_Qty
			SET @EOL_Qty_Map = @EOL_Qty_Map - @Order_Qty
			SET @FT_Qty_Map = @FT_Qty_Map - @Order_Qty
			SET @Assm_Qty_Map = @Assm_Qty_Map - @Order_Qty

			IF @FG_Qty_Map >= 0
			BEGIN
				SET @DaysToShip = 'STOCK'
			END
			ELSE
			BEGIN
				SET @DaysToShip = DATEDIFF( day, CURRENT_TIMESTAMP, @shecdule_Date)

			END
			UPDATE @RESULTS_TABLE SET [FG] = @FG_Qty_Map, [EOL] = @EOL_Qty_Map, [FT] = @FT_Qty_Map,[Assembly] = @Assm_Qty_Map, [Days to Ship] = @DaysToShip WHERE [Order No] = @Order_No AND [Line No] = @Line_No
		END
		FETCH NEXT FROM @Result_Cursor INTO @Ship_Req_No, @Order_No, @Line_No, @PART_NUMBER, @Order_Qty, @fg_qty, @eol_qty, @ft_qty, @assembly_qty, @shecdule_Date
	END
	CLOSE @Result_Cursor
	DEALLOCATE @Result_Cursor
	IF @CHIP_SET_VALUE = 'All'
	BEGIN
		select * from  @RESULTS_TABLE order by [Mfg Part Number],[Schedule Ship Date],[Order No],[Line No]--[Request Date]
	END
	ELSE
	BEGIN
		select * from  @RESULTS_TABLE WHERE [ChipSet] = @CHIP_SET_VALUE order by [Mfg Part Number],[Schedule Ship Date],[Order No],[Line No]--[Request Date]
	END
	
END
